import datetime
import pymysql

def migrate_index():
    db = pymysql.connect('localhost','root','minicooper','stock_info',charset="utf8")
    cursor = db.cursor()

    codes = []
    select_sql = "SELECT CODE FROM STOCK_INDEX"

    cursor.execute(select_sql)
    select_result = cursor.fetchall()

    for result_pickle in select_result:
        code = result_pickle[0]
        if code in codes:
            pass
        else:
            codes.append(code)

    for code in codes:
        create_table_sql = "CREATE TABLE IF NOT EXISTS {0} LIKE STOCK_INDEX".format(code)
        cursor.execute(create_table_sql)
        db.commit()
        select_index_sql = "SELECT * FROM STOCK_INDEX WHERE CODE = '{0}'".format(code)
        cursor.execute(select_index_sql)
        index_result = cursor.fetchall()
        migrate_sql = "INSERT INTO {0} (DATE,OPEN,CLOSE,HIGH,LOW,VOLUME,CODE) VALUES ".format(code)
        dates = []
        for index_result_pickle in index_result:
            date = datetime.datetime.strftime(index_result_pickle[0], "%Y-%m-%d")
            open = index_result_pickle[1]
            close = index_result_pickle[2]
            high = index_result_pickle[3]
            low = index_result_pickle[4]
            volume = index_result_pickle[5]
            Code = index_result_pickle[6]
            insert_sql_data = (date,open,close,high,low,volume,Code)
            if date not in dates:
                dates.append(date)
                migrate_sql += str(insert_sql_data) + ','
            else:
                pass
        migrate_sql = migrate_sql.rstrip(',')
        cursor.execute(migrate_sql)
        db.commit()
        print("{0} is finished!".format(code))

if __name__ == '__main__':
    migrate_index()